package com.viettel.core.dao.report;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.type.StandardBasicTypes;
import org.hibernate.type.Type;

import com.viettel.core.entities.report.vo.ReportServiceFilter;
import com.viettel.core.entities.report.vo.ReportServiceVO;
import com.viettel.core.exceptions.DataAccessException;
import com.viettel.core.repo.IRepository;

public class ReportServiceDAOImpl implements ReportServiceDAO{

	private IRepository repo;
	
	public void setRepo(IRepository repo) {
		this.repo = repo;
	}
	
	@Override
	public List<ReportServiceVO> getListReportService(ReportServiceFilter filter)
			throws DataAccessException {
		List<Object> selectParams = new ArrayList<Object>();
		StringBuilder selectSql = new StringBuilder();
		StringBuilder countSql = new StringBuilder();
		StringBuilder fromQuery = new StringBuilder();
		
		selectSql.append("SELECT ( \n");
		selectSql.append("  CASE type \n");
		selectSql.append("    WHEN 1 \n");
		selectSql.append("    THEN 'TEXT' \n");
		selectSql.append("    ELSE 'SUB' \n");
		selectSql.append("  END) AS serviceType, \n");
		selectSql.append("  gr.name groupName, \n");
		selectSql.append("  sv.name serviceName, \n");
		selectSql.append("  sv.description, \n");
		selectSql.append("  'Hoạt động' AS serviceStatus \n");
		fromQuery.append("FROM sms_service sv \n");
		fromQuery.append("JOIN sms_service_group gr \n");
		fromQuery.append("ON sv.group_id =gr.id \n");
		fromQuery.append("WHERE 1        =1 \n");	
		if (filter.getServiceType() != null
				&& !filter.getServiceType().equals(0)) {
			fromQuery.append("AND sv.type               = ? \n");
			selectParams.add(filter.getServiceType());
		}
		
		if (filter.getServiceId() != null
				&& !filter.getServiceId().equals(0)) {
			fromQuery.append("AND sv.id                 = ? \n");
			selectParams.add(filter.getServiceId());
		}
		if (filter.getGroupId() != null && !filter.getGroupId().equals(0)) {
			fromQuery.append("AND sv.group_id                 = ? \n");
			selectParams.add(filter.getGroupId());
		}

		if (filter.getServiceCode() != null) {
			fromQuery.append("AND ( lower(sv.service_code) LIKE (?) ESCAPE '/' \n");
			fromQuery.append("OR lower(sv.name) LIKE (?) ESCAPE '/' ) \n");
			selectParams.add("%"+ filter.getServiceCode().trim().toLowerCase() +"%");
			selectParams.add("%"+ filter.getServiceCode().trim().toLowerCase() + "%");
		}
		selectSql.append(fromQuery);
		selectSql.append("ORDER BY serviceType DESC, \n");
		selectSql.append("  gr.name, \n");
		selectSql.append("  sv.name");

		countSql.append("SELECT COUNT(*) AS COUNT ").append(fromQuery);
		

		String[] fieldNames = new String[] { "serviceType", "groupName", "serviceName"
				,"description","serviceStatus"};

		Type[] fieldTypes = new Type[] { StandardBasicTypes.STRING, StandardBasicTypes.STRING, StandardBasicTypes.STRING,
				StandardBasicTypes.STRING, StandardBasicTypes.STRING};

		List<ReportServiceVO> list = null;
		if (filter.getPaging() == null) {
			list = repo.getListByQueryAndScalar(ReportServiceVO.class,
					fieldNames, fieldTypes, selectSql.toString(), selectParams);
		} else {
			list = repo.getListByQueryAndScalarPaginated(
					ReportServiceVO.class, fieldNames, fieldTypes,
					selectSql.toString(), countSql.toString(), selectParams,
					selectParams, filter.getPaging());
		}
		return list;
	}

}
